﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Module;
using System.Data.SqlClient;
using System.Data;
using Qmaker.DAL;

namespace DLL
{
    public class DUserPost
    {
        #region SQL语句
        private const string SQL_INSERT = "insert into UserPost(UserPostId,UserPaperId,UserPassportId,Body,ToPostId,ToUserPassportId,Status,Bak1,Bak2,Bak3,LastUpdate,DateCreated) values (@UserPostId,@UserPaperId,@UserPassportId,@Body,@ToPostId,@ToUserPassportId,@Status,@Bak1,@Bak2,@Bak3,@LastUpdate,@DateCreated)";

        private const string SQL_UPDATE = "update UserPost set UserPaperId=@UserPaperId,UserPassportId=@UserPassportId,Body=@Body,ToPostId=@ToPostId,ToUserPassportId=@ToUserPassportId,Status=@Status,Bak1=@Bak1,Bak2=@Bak2,Bak3=@Bak3,LastUpdate=@LastUpdate,DateCreated=@DateCreated  where UserPostId=@UserPostId ";

        private const string SQL_DELETE = "delete from UserPost where UserPaperId=@UserPaperId ";

        private const string SQL_DELETE_ID = "delete from UserPost where UserPostId=@UserPostId ";

        private const string SQL_SELECT_PAPERID = "select  UserPostId,UserPaperId,p.UserPassportId,Body,ToPostId,ToUserPassportId,p.Status,p.Bak1,p.Bak2,p.Bak3,p.LastUpdate,p.DateCreated,u.UserName, up.NickName from UserPost p left join UserPassport u On p.UserPaperId=u.UserPassportId left join UserProfile up On up.UserPassportId=p.UserPassportId where UserPaperId=@UserPaperId and p.status=1";

        private const string SQL_SELECT_ID = "select  UserPostId,UserPaperId,p.UserPassportId,Body,ToPostId,ToUserPassportId,p.Status,p.Bak1,p.Bak2,p.Bak3,p.LastUpdate,p.DateCreated,u.UserName, up.NickName from UserPost p left join UserPassport u On p.UserPaperId=u.UserPassportId left join UserProfile up On up.UserPassportId=p.UserPassportId where UserPostId=@UserPostId";


        #endregion

        #region 公共接口

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="userPostInfo"></param>
        /// <returns></returns>
        public bool Insert(UserPostInfo userPostInfo)
        {
            if (userPostInfo == null)
                throw new ArgumentNullException("userPostInfo");

            SqlParameter[] parms = GetInsertParms(userPostInfo);
            //parms = SetInsertParmsCheck(parms);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        Convert.ToInt32(SqlHelper.ExecuteScalar(trans, CommandType.Text, SQL_INSERT, parms));
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="userPostInfo"></param>
        /// <returns></returns>
        public bool Update(UserPostInfo userPostInfo)
        {
            if (userPostInfo == null || userPostInfo.UserPassportId.ToString().Length != 18)
                throw new ArgumentNullException("userPostInfo");

            SqlParameter[] parms = GetInsertParms(userPostInfo);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SqlHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_UPDATE, parms);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }
        /// <summary>
        /// 根据文章Id删除所有回复
        /// </summary>
        /// <param name="userPaperId"></param>
        /// <returns></returns>
        public int DeleteByPagerId(long userPaperId)
        {
            SqlParameter[] parms = {
                    new SqlParameter("@UserPaperId",SqlDbType.BigInt)
                };
            parms[0].Value = userPaperId;

            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, SQL_DELETE, parms);

        }

        /// <summary>
        /// 删除单条回复
        /// </summary>
        /// <param name="userPostId"></param>
        /// <returns></returns>
        public int Delete(long userPostId)
        {
            SqlParameter[] parms = {
                    new SqlParameter("@UserPostId",SqlDbType.BigInt)
                };
            parms[0].Value = userPostId;

            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, SQL_DELETE_ID, parms);

        }

        /// <summary>
        /// 根据UserPaperId获取所有回复
        /// </summary>
        /// <param name="userPaperId"></param>
        /// <returns></returns>
        public List<UserPostInfo> GetAllPostByPaperId(long userPaperId)
        {
            List<UserPostInfo> posts = new List<UserPostInfo>();

            UserPostInfo userPostInfo = null;
            SqlParameter[] parms = {
                    new SqlParameter("@UserPaperId",SqlDbType.BigInt)
                };
            parms[0].Value = userPaperId;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_PAPERID, parms))
            {
                while (reader.Read())
                {
                    userPostInfo = GetInstanceByDataReader(reader);
                    if (userPostInfo != null && userPostInfo.UserPaperId != 0)
                        posts.Add(userPostInfo);
                }
            }
            return posts;
        }

        /// <summary>
        /// 根据PostId获取单条Comment
        /// </summary>
        /// <param name="userPostId"></param>
        /// <returns></returns>
        public UserPostInfo GetPostByUserPostId(long userPostId)
        {
            //List<UserPostInfo> posts = new List<UserPostInfo>();

            UserPostInfo userPostInfo = null;
            SqlParameter[] parms = {
                    new SqlParameter("@UserPostId",SqlDbType.BigInt)
                };
            parms[0].Value = userPostId;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_ID, parms))
            {
                while (reader.Read())
                {
                    userPostInfo = GetInstanceByDataReader(reader);
                    
                }
            }
            return userPostInfo;
        }


        #endregion


        #region 私有接口
        private SqlParameter[] SetInsertParmsCheck(SqlParameter[] parms)
        {
            for (int i = 0; i < parms.Length; i++)
            {
                if (parms[i].Value == null)
                {
                    parms[i].Value = DBNull.Value;
                }
                if (parms[i].DbType == DbType.DateTime && parms[i].Value.Equals(DateTime.MinValue))
                {
                    parms[i].Value = DBNull.Value;
                }
            }
            return parms;
        }

        private SqlParameter[] GetInsertParms(UserPostInfo model)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@UserPostId", SqlDbType.BigInt,8),
					new SqlParameter("@UserPaperId", SqlDbType.BigInt,8),
					new SqlParameter("@UserPassportId", SqlDbType.BigInt,8),
					new SqlParameter("@Body", SqlDbType.NVarChar,2147483640),
					new SqlParameter("@ToPostId", SqlDbType.BigInt,8),
					new SqlParameter("@ToUserPassportId", SqlDbType.BigInt,8),
					new SqlParameter("@Status", SqlDbType.Int,4),
					new SqlParameter("@Bak1", SqlDbType.NVarChar,50),
					new SqlParameter("@Bak2", SqlDbType.NVarChar,100),
					new SqlParameter("@Bak3", SqlDbType.NVarChar,200),
					new SqlParameter("@LastUpdate", SqlDbType.DateTime),
					new SqlParameter("@DateCreated", SqlDbType.DateTime)};
            parameters[0].Value = model.UserPostId;
            parameters[1].Value = model.UserPaperId;
            parameters[2].Value = model.UserPassportId;
            parameters[3].Value = model.Body;
            parameters[4].Value = model.ToPostId;
            parameters[5].Value = model.ToUserPassportId;
            parameters[6].Value = model.Status;
            parameters[7].Value = model.Bak1;
            parameters[8].Value = model.Bak2;
            parameters[9].Value = model.Bak3;
            parameters[10].Value = model.LastUpdate;
            parameters[11].Value = model.DateCreated;

            return SetInsertParmsCheck(parameters);
        }
        private UserPostInfo GetInstanceByDataReader(SqlDataReader reader)
        {
            UserPostInfo current = new UserPostInfo();
            //UserPostId,UserPaperId,UserPassportId,Body,ToPostId,ToUserPassportId,Status,Bak1,Bak2,Bak3,LastUpdate,DateCreated

            current.UserPostId = reader.IsDBNull(0) ? 0 : reader.GetInt64(0);
            current.UserPaperId = reader.IsDBNull(1) ? 0 : reader.GetInt64(1);
            current.UserPassportId = reader.IsDBNull(2) ? 0 : reader.GetInt64(2);
            current.Body = reader.IsDBNull(3) ? string.Empty : reader.GetString(3);
            current.ToPostId = reader.IsDBNull(4) ? 0 : reader.GetInt64(4);
            current.ToUserPassportId = reader.IsDBNull(5) ? 0 : reader.GetInt64(5);
            current.Status = reader.IsDBNull(6) ? 0 : reader.GetInt32(6);
            current.Bak1 = reader.IsDBNull(7) ? string.Empty : reader.GetString(7);
            current.Bak2 = reader.IsDBNull(8) ? string.Empty : reader.GetString(8);
            current.Bak3 = reader.IsDBNull(9) ? string.Empty : reader.GetString(9);
            current.LastUpdate = reader.IsDBNull(10) ? DateTime.MinValue : reader.GetDateTime(10);
            current.DateCreated = reader.IsDBNull(11) ? DateTime.MinValue : reader.GetDateTime(11);

            current.UserName = reader.IsDBNull(12) ? string.Empty : reader.GetString(12);
            current.NickName = reader.IsDBNull(13) ? string.Empty : reader.GetString(13);

            return current;
        }

        #endregion
    }
}
